const db = require('../db/index.js')
const util = require('util')
const query = util.promisify(db.query).bind(db)

exports.getusacovRouter = async (req, res) => {
    let { query: searchName, pagenum, pagesize, startdate, enddate } = req.query;
    let start = (pagenum - 1) * pagesize; //pagenum=2  pagesize=1
    let length = pagesize;
    let sql1
    let sql2
    let sqlerr
    var obj = {
        data: {},
        meta: {
            status: '',
            msg: ''
        }
    }
    if (searchName && startdate) {
        sql1 = `select * from covid19_daily_usa where countryName like '%${searchName}%' and DATE_FORMAT(datetime,'%Y-%m-%d') BETWEEN '${startdate}' AND '${enddate}' limit ${start},${length}`
        sql2 = `select * from covid19_daily_usa where countryName like '%${searchName}%' and DATE_FORMAT(datetime,'%Y-%m-%d') BETWEEN '${startdate}' AND '${enddate}'`
    } else if (!searchName && startdate) {
        sql1 = `select * from covid19_daily_usa where DATE_FORMAT(datetime,'%Y-%m-%d') BETWEEN '${startdate}' AND '${enddate}' limit ${start},${length}`
        sql2 = `select * from covid19_daily_usa where DATE_FORMAT(datetime,'%Y-%m-%d') BETWEEN '${startdate}' AND '${enddate}'`
    } else if (searchName && !startdate) {
        sql1 = `select * from covid19_daily_usa where countryName like '%${searchName}%' limit ${start},${length}`
        sql2 = `select * from covid19_daily_usa where countryName like '%${searchName}%'`
    } else {
        sql1 = `select * from covid19_daily_usa limit ${start},${length}`
        sql2 = `select * from covid19_daily_usa`
    }
    // if (searchName) {
    //     sql1 = `select * from covid19_daily_usa where countryName like '%${searchName}%' limit ${start},${length}`
    //     sql2 = `select * from covid19_daily_usa where countryName like '%${searchName}%'`
    // } else {
    //     sql1 = `select * from covid19_daily_usa limit ${start},${length}`
    //     sql2 = `select * from covid19_daily_usa`
    // }
    try {
        let results = await query(sql2)
        obj.data.total = results.length
        obj.data.pagenum = pagenum
    } catch (err) {
        sqlerr = err;
    }
    try {
        let results = await query(sql1)
        for (var i = 0; i < results.length; i++) {
            results[i] = {
                id: results[i].id,
                countryName: results[i].countryName,
                newCount: results[i].newCount,
                nowCount: results[i].nowCount,
                diagnosedCount: results[i].diagnosedCount,
                deadCount: results[i].deadCount,
                cureCount: results[i].cureCount,
                datetime: results[i].datetime,
            }
        }
        obj.data.usacovdata = results
    } catch (err) {
        sqlerr = err;
    }

    if (sqlerr) {
        console.log(sqlerr)
        obj.meta.status = 500;
        obj.meta.msg = "数据库查询错误";
        return res.send(obj);
    } else {
        obj.meta.status = 200;
        obj.meta.msg = "获取列表成功";
        return res.send(obj);
    }
}

exports.inquireusacovById = (req, res) => {
    let id = req.params.id;
    const query1 = `select * from covid19_daily_usa WHERE id = ?`
    var obj = {
        data: {},
        meta: {
            status: '',
            msg: ''
        }
    }
    db.query(query1, id, (err, results) => {
        if (err) {
            obj.meta.status = 500;
            obj.meta.msg = err;
            return res.send(obj);
        } else {
            if (results.length === 1) {
                var data1 = {
                    "id": results[0].id,
                    "countryName": results[0].countryName,
                    "newCount": results[0].newCount,
                    "nowCount": results[0].nowCount,
                    "diagnosedCount": results[0].diagnosedCount,
                    "deadCount": results[0].deadCount,
                    "cureCount": results[0].cureCount,
                    "datetime": results[0].datetime,
                }
                obj.meta.msg = '查询成功';
                obj.meta.status = 200;
                obj.data = data1;
                return res.send(obj)
            } else {
                obj.meta.status = 500;
                obj.meta.msg = '查询到了数据，但数据出错';
                return res.send(obj);
            }
        }
    })
}

exports.deleteusacovRouter = (req, res) => {
    let id = req.params.id
    let sql1 = 'delete from covid19_daily_usa where id=?'
    var obj = {
        data: {},
        meta: {
            status: '',
            msg: ''
        }
    }
    db.query(sql1, id, (err, results) => {
        if (err) {
            obj.meta.status = 500;
            obj.meta.msg = '数据库查询错误';
            return res.send(obj);
        } else {
            if (results.affectedRows == 1) {
                obj.meta.status = 200;
                obj.meta.msg = '删除成功';
                return res.send(obj);
            } else {
                obj.meta.status = 500;
                obj.meta.msg = '没有查询到数据或数据库内部错误';
                return res.send(obj);
            }
        }
    })
}

exports.addusacovRouter = (req, res) => {
    var obj = {
        data: {},
        meta: {
            status: '',
            msg: ''
        }
    }
    let { id, countryName, newCount, nowCount, diagnosedCount, deadCount, cureCount, datetime } = req.body;
    let param1 = {
        id, countryName, newCount, nowCount, diagnosedCount, deadCount, cureCount, datetime
    }
    let sql1 = "insert into covid19_daily_usa set ?"
    db.query(sql1, param1, (err, results) => {
        if (err) {
            console.log(err)
            obj.meta.status = 500;
            obj.meta.msg = err;
            return res.send(obj);
        } else {
            if (results.affectedRows == 1) {
                obj.meta.status = 200;
                obj.meta.msg = '添加成功';
                return res.send(obj);
            }
        }
    })
}


exports.updateusacovRouter = (req, res) => {
    var obj = {
        data: {},
        meta: {
            status: '',
            msg: ''
        }
    }
    let id = req.params.id;
    let { countryName, newCount, nowCount, diagnosedCount, deadCount, cureCount, datetime } = req.body
    let changeValue = {
        countryName: countryName,
        newCount: newCount,
        nowCount: nowCount,
        diagnosedCount: diagnosedCount,
        deadCount: deadCount,
        cureCount: cureCount,
        datetime: datetime
    }
    // console.log(req)
    let sql1 = "update covid19_daily_usa set ? where id = ?"
    db.query(sql1, [changeValue, id], (err, results) => {
        if (err) {
            console.log(err)
            obj.meta.status = 500;
            obj.meta.msg = '数据库查询错误';
            return res.send(obj);
        } else {
            if (results.affectedRows == 1) {
                obj.meta.status = 200;
                obj.meta.msg = '更新成功';
                return res.send(obj);
            }
        }
    })
}